﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace OrmAuto
{
    public class RicercheSql : ABaseSql
    {
        public RicercheSql()
        {
        }

        public DataTable spFindAnnVendita_MMPC(int pIdMarca, int pIdModello, int pIdPossesso, int pIdColore, int pPrezzoStart, int pPrezzoFinal)
        {
            List<SqlParameter> parametri = new List<SqlParameter>(4);

            parametri.Add(new SqlParameter("@pIdMarca", SqlDbType.Int) { Value = pIdMarca });
            parametri.Add(new SqlParameter("@pIdModello", SqlDbType.Int) { Value = pIdModello });
            parametri.Add(new SqlParameter("@pIdPossesso", SqlDbType.Int) { Value = pIdPossesso });
            parametri.Add(new SqlParameter("@pIdColore", SqlDbType.Int) { Value = pIdColore });
            parametri.Add(new SqlParameter("@pPrezzoStart", SqlDbType.Int) { Value = pPrezzoStart });
            parametri.Add(new SqlParameter("@pPrezzoFinal", SqlDbType.Int) { Value = pPrezzoFinal });

            try
            {
                return execStoredProc(OggettiDB.spFindAnnVendita_MMPC, parametri.ToArray());
            }
            catch (Exception ex)
            { }

            return null;

        }//fM

        public DataTable sp_vAnnVenditaGetBy(int pIdMarca, int pIdModello, int pIdPossesso, int pIdColore, 
           int pIdTipologia,int pIdStato, int pPrezzoStart, int pPrezzoFinal,int pKmStart,int pKmFine,
            bool pIvaDeducibile,bool pAbilitato)
        {
            List<SqlParameter> parametri = new List<SqlParameter>(4);

            parametri.Add(new SqlParameter("@pIdMarca", SqlDbType.Int) { Value = pIdMarca });
            parametri.Add(new SqlParameter("@pIdModello", SqlDbType.Int) { Value = pIdModello });
            parametri.Add(new SqlParameter("@pIdPossesso", SqlDbType.Int) { Value = pIdPossesso });
            parametri.Add(new SqlParameter("@pIdColore", SqlDbType.Int) { Value = pIdColore });
            parametri.Add(new SqlParameter("@pIdTipologia", SqlDbType.Int) { Value = pIdTipologia });
            parametri.Add(new SqlParameter("@pIdStato", SqlDbType.Int) { Value =  pIdStato });
            parametri.Add(new SqlParameter("@pPriceStart", SqlDbType.Int) { Value = pPrezzoStart });
            parametri.Add(new SqlParameter("@pPriceFinal", SqlDbType.Int) { Value = pPrezzoFinal });
            parametri.Add(new SqlParameter("@pKmStart", SqlDbType.Int) { Value = pKmStart });
            parametri.Add(new SqlParameter("@pKmFine", SqlDbType.Int) { Value = pKmFine });
            parametri.Add(new SqlParameter("@pIvaDed", SqlDbType.Bit) { Value = pIvaDeducibile });
            parametri.Add(new SqlParameter("@pAbilitato", SqlDbType.Bit) { Value = pAbilitato  });

            try
            {
                return execStoredProc(OggettiDB.sp_vAnnVenditaGetBy, parametri.ToArray());
            }
            catch (Exception ex)
            {
                ;
            }

            return null;

        }//fM

        public DataTable sp_vAnnAcquistoGetBy(int pIdMarca, int pIdModello, int pIdPossesso, int pIdColore,
                int pIdTipologia, int pIdStato, int pPrezzoStart, int pPrezzoFinal, int pKmStart, int pKmFine,
                 bool pIvaDeducibile, bool pAbilitato)
        {
            List<SqlParameter> parametri = new List<SqlParameter>(4);

            parametri.Add(new SqlParameter("@pIdMarca", SqlDbType.Int) { Value = pIdMarca });
            parametri.Add(new SqlParameter("@pIdModello", SqlDbType.Int) { Value = pIdModello });
            parametri.Add(new SqlParameter("@pIdPossesso", SqlDbType.Int) { Value = pIdPossesso });
            parametri.Add(new SqlParameter("@pIdColore", SqlDbType.Int) { Value = pIdColore });
            parametri.Add(new SqlParameter("@pIdTipologia", SqlDbType.Int) { Value = pIdTipologia });
            parametri.Add(new SqlParameter("@pIdStato", SqlDbType.Int) { Value = pIdStato });
            parametri.Add(new SqlParameter("@pPriceStart", SqlDbType.Int) { Value = pPrezzoStart });
            parametri.Add(new SqlParameter("@pPriceFinal", SqlDbType.Int) { Value = pPrezzoFinal });
            parametri.Add(new SqlParameter("@pKmStart", SqlDbType.Int) { Value = pKmStart });
            parametri.Add(new SqlParameter("@pKmFine", SqlDbType.Int) { Value = pKmFine });
            parametri.Add(new SqlParameter("@pIvaDed", SqlDbType.Bit) { Value = pIvaDeducibile });
            parametri.Add(new SqlParameter("@pAbilitato", SqlDbType.Bit) { Value = pAbilitato });

            try
            {
                return execStoredProc(OggettiDB.sp_vAnnAcquistoGetBy , parametri.ToArray());
            }
            catch (Exception ex)
            { }

            return null;

        }//fM


        /// <summary>
        /// Immettere il valore 0 per avere tutte le marche
        /// pMarcheNotShow acccetta valori separati da virgole clausola IN del SQl
        /// </summary>
        /// <param name="pIdMarca"></param>
        /// <returns></returns>
        public DataTable sp_vUltAnnVenditaPerMarca(int pIdMarca,string pMarcheNotShow,int pQuanteMostro)
        {
            List<SqlParameter> parametri = new List<SqlParameter>(1);
            parametri.Add(new SqlParameter("@IdMarca", SqlDbType.Int) { Value = pIdMarca });
            parametri.Add(new SqlParameter("@pMarcheNot", SqlDbType.VarChar) { Value = pMarcheNotShow });
            parametri.Add(new SqlParameter("@pQuanteMostro", SqlDbType.Int) { Value = pQuanteMostro });

            try
            {
                return execStoredProc(OggettiDB.sp_vUltAnnVenditaPerMarca, parametri.ToArray());
            }
            catch (Exception ex)
            { }

            return null;

        }//fM

    }//fC
}
